package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Competition;
import cn.edu.lingnan.pojo.Participation;
import cn.edu.lingnan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class IParticipationDao implements ParticipationDao{
    @Override
    public int addParticipation(int student_id, int competition_id,String competition_name) {
        int result = 0;
        Connection conn = null;
        PreparedStatement psSelect = null;
        PreparedStatement psInsert = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            // 先查询学生是否已经选过该比赛项目
            psSelect = conn.prepareStatement("SELECT * FROM participation WHERE student_id = ? AND competition_id = ?");
            psSelect.setInt(1, student_id);
            psSelect.setInt(2, competition_id);
            rs = psSelect.executeQuery();
            if (!rs.next()) {
                // 若无选过的记录，再进行插入
                psInsert = conn.prepareStatement("INSERT INTO participation (student_id, competition_id,competition_name) VALUES (?, ?, ?)");
                psInsert.setInt(1, student_id);
                psInsert.setInt(2, competition_id);
                psInsert.setString(3,competition_name);
                psInsert.executeUpdate();
                result = 1;
            }
        } catch (SQLException e) {
            // 可以根据具体情况处理异常，比如记录日志等
            throw new RuntimeException("Failed to add participation", e);
        } finally {
            DBUtil.getInstance().close(rs, psInsert, conn);
            if(psSelect!=null){
                try {
                    psSelect.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return result;
    }


    @Override
        //按比赛名称查找参赛记录的方法
        public Participation findParticipation(String competition_name) {
            Participation participation = null;
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                connection = DBUtil.getInstance().getConnection();
                String query = "SELECT * FROM participation WHERE competition_name LIKE ?";
                PreparedStatement PreparedStatement = connection.prepareStatement(query);
                preparedStatement.setString(1, "%" + competition_name + "%");
                resultSet = preparedStatement.executeQuery();

                while (resultSet.next()) {
                    participation = new Participation();
                    participation.setId(resultSet.getInt("id"));
                    participation.setStudent_id(resultSet.getInt("student_id"));
                    participation.setCompetition_id(resultSet.getInt("competition_id"));
                    participation.setCompetition_name(resultSet.getString("competition_name"));
                    participation.setScore(resultSet.getFloat("score"));
                    participation.setCompetition_rank(resultSet.getInt("competition_rank"));
                    participation.setStatus(resultSet.getInt("status"));
            }

        } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            finally {
                DBUtil.getInstance().close(resultSet, preparedStatement, connection);
            }
            return participation;
    }
    @Override
    public Participation findParticipationById(int id) {
        Participation participation = null;

        ResultSet rs = null;
        PreparedStatement stmt = null;
        try (Connection conn = DBUtil.getInstance().getConnection()) {
            stmt = conn.prepareStatement("SELECT * FROM participation WHERE id = ?");
            stmt.setInt(1, id);
            rs = stmt.executeQuery();

            if (rs.next()) {
                participation = new Participation();
                participation.setId(rs.getInt("id"));
                participation.setStudent_id(rs.getInt("student_id"));
                participation.setCompetition_id(rs.getInt("competition_id"));
                participation.setCompetition_name(rs.getString("competition_name"));
                participation.setScore(rs.getFloat("score"));
                participation.setCompetition_rank(rs.getInt("competition_rank"));
                participation.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.getInstance().close(rs, stmt, null);
        }

        return participation;
    }

    @Override
    public List<Participation> findParticipationBySid(int sid) {
        List<Participation> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getInstance().getConnection();
            String sql = "SELECT * FROM participation WHERE student_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, sid);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Participation participation = new Participation();
                participation.setId(resultSet.getInt("id"));
                participation.setStudent_id(resultSet.getInt("student_id"));
                participation.setCompetition_id(resultSet.getInt("competition_id"));
                participation.setCompetition_name(resultSet.getString("competition_name"));
                participation.setScore(resultSet.getFloat("score"));
                participation.setCompetition_rank(resultSet.getInt("competition_rank"));
                participation.setStatus(resultSet.getInt("status"));
                list.add(participation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.getInstance().close(resultSet, preparedStatement, connection);
        }

        return list;
    }

    @Override
    public List<Participation> findParticipationAll() {
        List<Participation> participations = new ArrayList<>();
        String sql = "SELECT * FROM participation";
        try (Connection conn = DBUtil.getInstance().getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Participation participation = new Participation();
                participation.setId(rs.getInt("id"));
                participation.setStudent_id(rs.getInt("student_id"));
                participation.setCompetition_id(rs.getInt("competition_id"));
                participation.setCompetition_name(rs.getString("competition_name"));
                participation.setScore(rs.getFloat("score"));
                participation.setCompetition_rank(rs.getInt("competition_rank"));
                participation.setStatus(rs.getInt("status"));
                participations.add(participation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return participations;
    }

    @Override
    public boolean delParticipation(int id) {
        String sql = "DELETE FROM participation WHERE id = ?";
        try (Connection connection = DBUtil.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, id);
            int rowsAffected = preparedStatement.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    @Override
    public int updateParticipation(Participation pt) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getInstance().getConnection();
            String query = "UPDATE participation SET student_id = ?, competition_id = ?, " +
                    "competition_name = ?, score = ?, competition_rank = ?, status = ? WHERE id = ?";
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(1, pt.getStudent_id());
            preparedStatement.setInt(2, pt.getCompetition_id());
            preparedStatement.setString(3, pt.getCompetition_name());
            preparedStatement.setDouble(4, pt.getScore());
            preparedStatement.setInt(5, pt.getCompetition_rank());
            preparedStatement.setInt(6, pt.getStatus());
            preparedStatement.setInt(7, pt.getId());

            int rowsAffected = preparedStatement.executeUpdate();

            return rowsAffected; // Directly return the number of rows affected
        } catch (SQLException e) {
            e.printStackTrace();
            return 0; // Return 0 on error
        } finally {
            DBUtil.getInstance().close(null,preparedStatement,connection);
        }
    }
    @Override
    public List<Participation> findParticipationByName(String name) {
        List<Participation> List = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("select * from participation where competition_name like concat('%',?,'%')");
            ps.setString(1,name);
            rs=ps.executeQuery();
            while(rs.next()){
                Participation participation = new Participation();
                participation.setId(rs.getInt("id"));
                participation.setStudent_id(rs.getInt("student_id"));
                participation.setCompetition_id(rs.getInt("competition_id"));
                participation.setCompetition_name(rs.getString("competition_name"));
                participation.setScore(rs.getFloat("score"));
                participation.setCompetition_rank(rs.getInt("competition_rank"));
                participation.setStatus(rs.getInt("status"));
                List.add(participation);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return List;
    }

    @Override
    public List<Participation> findParticipationByNameStatus(String name) {
        List<Participation> List = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("select * from participation where status=1 and competition_name like concat('%',?,'%')");
            ps.setString(1,name);
            rs=ps.executeQuery();
            while(rs.next()){
                Participation participation = new Participation();
                participation.setId(rs.getInt("id"));
                participation.setStudent_id(rs.getInt("student_id"));
                participation.setCompetition_id(rs.getInt("competition_id"));
                participation.setCompetition_name(rs.getString("competition_name"));
                participation.setScore(rs.getFloat("score"));
                participation.setCompetition_rank(rs.getInt("competition_rank"));
                participation.setStatus(rs.getInt("status"));
                List.add(participation);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return List;    }

    @Override
    public boolean updateScore(int pid,int score) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DBUtil.getInstance().getConnection();
            String sql = "UPDATE participation SET score = ? WHERE id = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, score);
            ps.setInt(2,pid);

            int rowsAffected = ps.executeUpdate();
            if(rowsAffected>0){
                return true;
            }else return false;

        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Return 0 on error
        } finally {
            DBUtil.getInstance().close(null,ps,connection);
        }
    }

    @Override
    public boolean updateRank(int sid,String cName, int rank) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DBUtil.getInstance().getConnection();
            String sql = "UPDATE participation SET competition_rank = ? WHERE student_id = ? and competition_name = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,rank);
            ps.setInt(2,sid);
            ps.setString(3,cName);

            int rowsAffected = ps.executeUpdate();
            if(rowsAffected>0){
                return true;
            }else return false;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.getInstance().close(null,ps,connection);
        }
    }
}
